﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
//数据访问层
namespace DAL
{
    public class DBHelper
    {
        //连接字符串
        private const string STR = @"Data Source=.;Initial Catalog=LirayDb;Integrated Security=True";
        private static SqlConnection connection;
        //返回数据库连接
        public static SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(STR);
                }
                return connection;
            }
        }
        //打开数据库
        public static void ConnectionOpen()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                //打开数据库
                Connection.Open();
            }
            else if (Connection.State == ConnectionState.Broken)
            {
                Connection.Close();
                Connection.Open();
            }
        }
        //关闭数据库
        public static void ConnectionClose()
        {
            if (Connection.State != ConnectionState.Closed)
            {
                //关闭数据库
                Connection.Close();
            }
        }
        //查询结果中的第一行第一列的值
        public static object ExecuteScalar(string sql)
        {
            try
            {
                //创建Command对象
                using (SqlCommand comm = new SqlCommand(sql, Connection))
                {
                    //打开数据库
                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Close();
                        Connection.Open();
                    }

                    //执行
                    return comm.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                //查询异常
                return -1;
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed)
                {
                    Connection.Close();
                }
            }
        }
        //执行SQL命令，返回受影响行数，用于增删改
        public static int ExecuteNonQuery(string sql)
        {
            try
            {
                using (SqlCommand comm = new SqlCommand(sql, Connection))
                {
                    //打开数据库
                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Close();
                        Connection.Open();
                    }
                    //执行
                    return comm.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //异常
                return -1;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    Connection.Close();
                }
            }
        }
        //查询多行多列
        public static SqlDataReader ExecuteReader(string sql)
        {
            try
            {
                //创建Command对象
                using (SqlCommand comm = new SqlCommand(sql, Connection))
                {
                    //打开数据库
                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Close();
                        Connection.Open();
                    }

                    //执行
                    return comm.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                //查询异常
                return null;
            }
        }
    }
}
